package com.handy.plugin.tree;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.handy.database.DBUtil;

public class TreeBaseDAO {

	public TreeBaseDAO(String tablename){
		this.tablename = tablename;
	}
	
	public TreeBaseDAO(){
		
	}
	
	@SuppressWarnings("unchecked")
	public TreeBaseBean findById(Integer id) throws SQLException{
		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		StringBuffer sb = new StringBuffer();

		sb.append("select * from ").append(getTablename()).append(" ");
		sb.append("where id=").append(id);

		BeanHandler h = new BeanHandler(TreeBaseBean.class);

		TreeBaseBean _bean = (TreeBaseBean) run.query(sb.toString(), h);
		
//		if (log.isDebugEnabled()){
//			if (null != _bean){
//				log.info("id="+_bean.getId());
//				log.info("pid="+_bean.getPid());
//				log.info("havecid="+_bean.getHavecid());
//				log.info("oid="+_bean.getOid());
//				log.info("alevel="+_bean.getAlevel());
//			}
//		}
		
		return _bean;
	}
	
	/**
	 * 调用的时间，传入的bean为要添加的节点的父节点。
	 * 返回的是要添加的TreeBaseBean
	 * @return
	 * @throws SQLException 
	 */
	public TreeBaseBean insert(Integer pid) throws SQLException{
		TreeBaseBean parentBean = findById(pid);
		
		if (null == parentBean){
			log.error("找不到要添加节点的父节点.");
			return null;
		}
		TreeBaseBean bean = new TreeBaseBean();
		//得到新节点的oid
		int oid = findCurrentOid(parentBean);
		updateOid(oid);//将比该oid大的节点所有oid+1
		bean.setOid(oid);
		bean.setAlevel(parentBean.getAlevel()+1);//将level+1
		bean.setHavecid(0);
		bean.setPid(pid);
		
		
		//将父节点改为有子
		StringBuffer sb = new StringBuffer();
		sb.append("update ").append(getTablename()).append(" set ");
		sb.append("havecid=1 ").append("where id = ").append(pid);
		
		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		run.update(sb.toString());
		
		return bean;
	}
	
	public void delete(Integer id) throws SQLException{

		StringBuffer sb = new StringBuffer();
		//找到要删除的节点信息
		TreeBaseBean bean = findById(id);
		
		if (null == bean){
			log.error("找不到要删掉节点的信息.");
		}
		
		//1.将要删除的节点改为删除状态 state=1
		sb.append("update ").append(getTablename()).append(" set ");
		sb.append("state=1 ").append("where id = ").append(id);
		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		run.update(sb.toString());
		//2.判断该节点的父节点是否还有子，没有则改havecid=0
		try {
			sb = new StringBuffer();
			sb.append("select count(id) from ").append(getTablename()).append(" ");
			sb.append("where pid = ").append(bean.getPid()).append(" ");
			sb.append("and state!=1");
			Object[] counts = (Object[]) run.query(sb.toString(),
					new ArrayHandler());
			int havecid = ((BigDecimal) counts[0]).intValue();
			if (havecid==0){//如果没有子
				sb = new StringBuffer();
				sb.append("update ").append(getTablename()).append(" ");
				sb.append("set havecid=0").append(" ");
				sb.append("where id=").append(bean.getPid());
				run.update(sb.toString());
			}
		} catch (Exception re) {
			log.error("get failed", re);
		}
		//3.删除该节点下的子节点,设置state=1
		try {
			//update h_auth_trees   set  state=1 where id in (select id from h_auth_trees  start with id = 141 connect by pid= prior id )
			sb = new StringBuffer();
			sb.append("update ").append(getTablename()).append(" set ");
			sb.append("state=1 ").append(" where id in (select id from ").append(getTablename());
			sb.append(" start with id = ").append(id).append(" connect by pid= prior id )");
			run.update(sb.toString());
		} catch (Exception re) {
			log.error("get failed", re);
		}
	}
	
	
	@SuppressWarnings("unchecked")
	public void upPosition(Integer id) throws SQLException{
		TreeBaseBean bean = findById(id);
		if (null == bean){
			log.error("找不到要上移的节点");
			return;
		}
		
		StringBuffer sb = new StringBuffer();
		sb.append("select * from ").append(getTablename()).append(" t ");
		sb.append("where t.oid<=").append(bean.getOid()).append(" ");
		sb.append("and t.alevel=").append(bean.getAlevel()).append(" ");
		sb.append("order by t.oid desc");
		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		BeanListHandler h = new BeanListHandler(TreeBaseBean.class);
		List<TreeBaseBean> list = (ArrayList<TreeBaseBean>) run.query(sb.toString(), h);
		if (list.size() >= 2) {
			TreeBaseBean node1 = list.get(1);
			downPosition(node1);
		}
	}
	
	public void downPosition(Integer id) throws SQLException {
		TreeBaseBean bean = findById(id);
		downPosition(bean);
	}
	
	
	@SuppressWarnings("unchecked")
	public void downPosition(TreeBaseBean bean) throws SQLException{
		if (null == bean) {
			log.error("找不到要移动的节点");
			return ;
		}
		
		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		StringBuffer sb = new StringBuffer();
		int downCount = 0;
		int upCount = 0;
		
		//找到该节点的所有oid比自已在的兄弟，包括本身。
		sb.append("select * from ").append(getTablename()).append(" t ");
		sb.append("where t.oid>=").append(bean.getOid()).append(" and ");
		sb.append("t.alevel=").append(bean.getAlevel()).append(" and ");
		sb.append("t.pid=").append(bean.getPid()).append(" ");
		sb.append("order by t.oid");

		BeanListHandler h = new BeanListHandler(TreeBaseBean.class);
		List<TreeBaseBean> list = (ArrayList<TreeBaseBean>) run.query(sb.toString(), h);
		//如果记录数大于或等于2则表示有兄弟，否则就不用处理。
		if (list.size() >= 2) {
			TreeBaseBean node1 = list.get(0);//本节点
			TreeBaseBean node2 = list.get(1);//该节点下面的第一个兄弟
			upCount = node2.getOid() - node1.getOid();//相差多少位
			
			if (list.size() >= 3) {//如果有超过两个兄弟
				TreeBaseBean node3 = list.get(2);
				downCount = node3.getOid() - node2.getOid();
				sb = new StringBuffer();
				sb.append("update ").append(getTablename()).append(" ");
				sb.append("set oid = -oid where oid >= ").append(node1.getOid()).append(" ");
				sb.append("and oid < ").append(node2.getOid());
				run.update(sb.toString());
				
				sb = new StringBuffer();
				sb.append("update ").append(getTablename()).append(" ");
				sb.append("set oid = oid-").append(upCount).append(" ");
				sb.append("where oid >= ").append(node2.getOid()).append(" ");
				sb.append("and oid < ").append(node3.getOid());
				run.update(sb.toString());
				
				sb = new StringBuffer();
				sb.append("update ").append(getTablename()).append(" ");
				sb.append("set oid = (0-oid)+").append(downCount).append(" ");
				sb.append("where  oid > ").append(0 - node2.getOid()).append(" ");
				sb.append("and oid <= ").append(0 - node1.getOid());
				run.update(sb.toString());
			} else if (list.size() == 2) {//如果只有两兄弟
				sb = new StringBuffer();
				if (bean.getAlevel() == 1) {//如果为第一级菜单，只需要找到最大的oid则可。
					sb.append("select max(oid) from ").append(getTablename());
				} else {//级别大于1
					sb.append("select min(t.oid) from ").append(getTablename()).append(" t ");
					sb.append("where t.alevel < ").append(bean.getAlevel()).append(" and ");
					sb.append("t.oid > ").append(node2.getOid()).append("  order by oid");
				}
				Object[] count = (Object[]) run.query(sb.toString(), new ArrayHandler());
				BigDecimal sum = (BigDecimal) count[0];
				if (sum == null) {//找不到相应记录
					sb = new StringBuffer();
					sb.append("select max(oid) from ").append(getTablename());
					Object[] count_1 = (Object[]) run.query(sb.toString(),new ArrayHandler());
					sum = (BigDecimal) count_1[0];
					downCount = sum.intValue() + 1 - node2.getOid();
				} else {
					downCount = sum.intValue() + 1 - node2.getOid();
					if (bean.getAlevel() != 1) {
						downCount = downCount - 1;
					}
				}
				sb = new StringBuffer();
				sb.append("update ").append(getTablename()).append(" ");
				sb.append("set oid = -oid where oid >= ").append(node1.getOid()).append(" ");
				sb.append("and oid < ").append(node2.getOid());
				run.update(sb.toString());
				
				sb = new StringBuffer();
				sb.append("update ").append(getTablename()).append(" ");
				sb.append("set oid = oid -").append(upCount).append(" ");
				sb.append("where oid >= ").append(node2.getOid()).append(" ");
				sb.append(" and oid < ").append(node2.getOid() + downCount);
				run.update(sb.toString());
				
				sb = new StringBuffer();
				sb.append("update ").append(getTablename()).append(" ");
				sb.append("set oid = (0-oid)+").append(downCount).append(" ");
				sb.append("where  oid > ").append(0 - node2.getOid()).append(" ");
				sb.append("and oid <= ").append(0 - node1.getOid());
				run.update(sb.toString());
			}
		} 
	}
	
	
	
	/**
	 * 添加节点时该节点oid的算法
	 * @param parentBean 父结点对象
	 * @return 返回当前的最大的oid
	 */
	public int findCurrentOid(TreeBaseBean parentBean) {


		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		int oid = 0;
		
		try {
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT count(*) FROM ").append(getTablename()).append(" c");
			sb.append(" where c.oid>").append(parentBean.getOid());
			sb.append(" AND c.alevel<=").append(parentBean.getAlevel());
			
			Object[] _count = (Object[]) run.query(sb.toString(),
					new ArrayHandler());
			int nCount = ((BigDecimal) _count[0]).intValue();
			
			sb = new StringBuffer();
			if (nCount > 0) {
				sb.append("SELECT c.oid FROM ").append(getTablename()).append(" c");
				sb.append(" where c.oid>").append(parentBean.getOid());
				sb.append(" AND c.alevel<=").append(parentBean.getAlevel());
				sb.append(" order by c.oid");
				// oracle方式
				String sql = "select oid from ( select oid from (" + sb.toString()
						+ ") ) where rownum<2";
				_count = (Object[]) run.query(sql, new ArrayHandler());
				if (_count != null) {
					oid = ((BigDecimal) _count[0]).intValue();
				}
			} else {
				sb.append("select max(oid) FROM ").append(getTablename());
				_count = (Object[]) run.query(sb.toString(),
						new ArrayHandler());
				if (_count != null) {
					oid = ((BigDecimal) _count[0]).intValue();
				}
				oid = oid + 1;
			}
		} catch (Exception e) {
			log.error(e.getMessage());
		}
		return oid;
	}
	
	/**
	 * 将比oid大的oid号全部加1
	 * 
	 * @param oid
	 */
	public void updateOid(int oid) {
		StringBuffer sb = new StringBuffer();
		sb.append("update ").append(getTablename()).append(" ");
		sb.append("set oid = oid + 1 ");
		sb.append("where oid >= ").append(oid);

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		try {
			run.update(sb.toString());
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
	}
	
	/**
	 * 判断该节点是否还有子节点
	 * 
	 * @param id
	 *            该结点的编号
	 * @return
	 */
	public boolean isPid(int id) {
		boolean ispid = false;
		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT count(*) FROM ").append(getTablename()).append(" c ");
		sb.append("where c.pid = ").append(id);
		try {
			Object[] _count = (Object[]) run.query(sb.toString(),
					new ArrayHandler());
			int count = ((BigDecimal) _count[0]).intValue();
			if (count > 0) {
				ispid = true;
			}
		} catch (Exception e) {
			log.error(e.getMessage());
		}
		return ispid;
	}
	
	
	
	private final Log log = LogFactory.getLog(TreeBaseDAO.class);
	protected DBUtil dbUtil = DBUtil.getInstance();
	
	private String tablename;
	private String dataSourceName;

	public String getDataSourceName() {
		return dataSourceName;
	}

	public void setDataSourceName(String dataSourceName) {
		this.dataSourceName = dataSourceName;
	}

	public String getTablename() {
		return tablename;
	}

	public void setTablename(String tablename) {
		this.tablename = tablename;
	}

}

